package com.xmsx.back.mapper.index;

import com.xmsx.back.pojo.Element;
import com.xmsx.back.pojo.ElementTotal;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;
import org.springframework.cache.annotation.Cacheable;

import java.util.List;

@Mapper
public interface GradMapper {

    /**
     * 查询年份列表
     *
     * @return
     */
    @Cacheable(cacheNames = "YearList")
    @Select("select distinct grad_time from graduate order by grad_time asc")
    List<Integer> getYearList();

    /**
     * 按起止日期和本科/硕士查询毕业去向数据
     *
     * @param start
     * @param end
     * @param edu
     * @return
     */
    @Cacheable(cacheNames = "DirectionData")
    @Select("select direction term, count(1) num " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and edu_backgro = #{edu} " +
            "group by direction " +
            "order by num desc")
    List<Element> getDirectionDataByYearsAndEdu(int start, int end, String edu);

    /**
     * 按起止日期和境内/境外查询升学单位数据
     *
     * @param start
     * @param end
     * @param bound
     * @return
     */
    @Cacheable(cacheNames = "SchoolData")
    @Select("select org_name term, count(1) num " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and direction = #{bound} " +
            "group by org_name " +
            "order by num desc")
    List<Element> getSchoolDataByYearsAndBound(int start, int end, String bound);

    /**
     * 按起止日期和本科/硕士查询单位性质分布
     *
     * @param start
     * @param end
     * @param edu
     * @return
     */
    @Cacheable(cacheNames = "OrgKindData")
    @Select("select distinct org_kind term, count(1) num " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and edu_backgro = #{edu} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "group by org_kind " +
            "order by num desc")
    List<Element> getOrgKindDataByYearsAndEdu(int start, int end, String edu);

    /**
     * 按起止日期和本科/硕士查询单位行业分布
     *
     * @param start
     * @param end
     * @param edu
     * @return
     */
    @Cacheable(cacheNames = "OrgIndustryData")
    @Select("select distinct org_industry term, count(1) num " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and edu_backgro = #{edu} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "group by org_industry " +
            "order by num desc")
    List<Element> getOrgIndustryDataByYearsAndEdu(int start, int end, String edu);

    /**
     * 按起止日期和本科/硕士查询就业单位分布
     *
     * @param start
     * @param end
     * @param edu
     * @return
     */
    @Cacheable(cacheNames = "OrgNameData")
    @Select("select distinct org_name term, count(1) num " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and edu_backgro = #{edu} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "group by org_name " +
            "order by num desc limit 10")
    List<Element> getOrgNameDataByYearsAndEdu(int start, int end, String edu);

    /**
     * 按起止日期查询就业单位分布
     *
     * @param start
     * @param end
     * @return
     */
    @Cacheable(cacheNames = "OrgNameDataTotal")
    @Select("select distinct org_name term, count(1) num " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "group by org_name " +
            "order by num desc limit 10")
    List<ElementTotal> getOrgNameDataByYears(int start, int end);

    /**
     * 按起止日期和本科/硕士查询就业地域分布
     *
     * @param start
     * @param end
     * @param edu
     * @return
     */
    @Cacheable(cacheNames = "OrgLocationData")
    @Select("select distinct LEFT(org_location,2) term, count(1) num " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and edu_backgro = #{edu} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "group by term " +
            "order by num desc")
    List<Element> getOrgLocationDataByYearsAndEdu(int start, int end, String edu);

    /**
     * 按起止日期、本科/硕士和单位名称查询该单位就业毕业生数量
     *
     * @param start
     * @param end
     * @param edu
     * @param orgName
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and edu_backgro = #{edu} " +
            "and org_name = #{orgName}")
    int getOrgNameNumByYearsAndEduAndOrgName(int start, int end, String edu, String orgName);

    /**
     * 按起止日期查询毕业生就业热门城市
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select distinct substring_index(substring_index(substring_index(org_location,'市',1),'省',-1),'区',-1) term, count(1) num " +
            "from graduate " +
            "where (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "and grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "group by term " +
            "order by num desc limit 5")
    List<Element> getHotCity(int start, int end);

    /**
     * 按年份和本科/硕士查询毕业生数量
     *
     * @param year
     * @param edu
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time = #{year} " +
            "and edu_backgro = #{edu}")
    int countByYearAndEdu(int year, String edu);

    /**
     * 按年份和本科/硕士查询就业数量
     *
     * @param year
     * @param edu
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time = #{year} " +
            "and edu_backgro = #{edu} " +
            "and direction is not null")
    int countEmployedByYearAndEdu(int year, String edu);

    /**
     * 按年份查询本科生升学数量
     *
     * @param year
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time = #{year} " +
            "and edu_backgro = '本科生毕业' " +
            "and (direction = '境内升学' or direction = '出国、出境')")
    int countUpgradeByYearBachelor(int year);

    /**
     * 按年份和本科/硕士查询
     *
     * @param year
     * @param edu
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time = #{year} " +
            "and edu_backgro = #{edu} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业')")
    int countContractEmployedByYearAndEdu(int year, String edu);

    /**
     * 按年份和本科/硕士查询在山东协议和合同就业的数量
     *
     * @param year
     * @param edu
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where LEFT(org_location,2) = '山东' " +
            "and grad_time = #{year} " +
            "and edu_backgro = #{edu} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业')")
    int countContractEmployedAtSDByYearAndEdu(int year, String edu);

    /**
     * 按起止日期和本科/硕士查询毕业生数量
     *
     * @param start
     * @param end
     * @param edu
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and edu_backgro = #{edu}")
    int countByYearsAndEdu(int start, int end, String edu);

    /**
     * 按起止日期和性别查询毕业生数量
     *
     * @param start
     * @param end
     * @param sex
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and sex = #{sex}")
    int countByYearsAndSex(int start, int end, String sex);

    /**
     * 按起止日期查询回生源地就业毕业生数量
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and LEFT(origin,2) = LEFT(org_location,2) " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业')")
    int countBackToOriginNumByYears(int start, int end);

    /**
     * 按起止日期查询协议和合同就业毕业生数量
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业')")
    int countContractEmployedByYears(int start, int end);

    /**
     * 按起止日期查询在鲁协议和合同就业毕业生数量
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "and LEFT(origin,2) = '山东' and LEFT(origin,2) = LEFT(org_location,2)")
    int countContractEmployedAtSDByYearsSDer(int start, int end);

    /**
     * 按起止日期查询生源地为山东且在鲁协议和合同就业毕业生数量
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "and LEFT(origin,2) = '山东'")
    int countContractEmployedByYearsSDer(int start, int end);

    /**
     * 按起止日期查询非山东籍毕业生回生源地就业数量
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and LEFT(origin,2) = LEFT(org_location,2) " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "and LEFT(origin,2) != '山东'")
    int countContractEmployedBackToOriginByYearsUnSDer(int start, int end);

    /**
     * 按起止日期查询非山东籍协议和合同就业毕业生数量
     *
     * @param start
     * @param end
     * @return
     */
    @Select("select count(1) " +
            "from graduate " +
            "where grad_time >= #{start} " +
            "and grad_time <= #{end} " +
            "and (direction = '签就业协议形式就业' or direction = '签劳动合同形式就业') " +
            "and LEFT(origin,2) != '山东'")
    int countContractEmployedByYearsUnSDer(int start, int end);
}
